package com.yuanren.em.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.yuanren.em.entity.MemberNotification;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Delete;

import java.util.List;
import java.util.Map;

/**
 * 会员通知Mapper接口
 */
public interface MemberNotificationMapper extends BaseMapper<MemberNotification> {

    /**
     * 分页查询会员通知（带用户信息）
     */
    @Select("SELECT * FROM t_member_notification n " +
            "WHERE 1=1 " +
            "AND (#{notificationType} IS NULL OR n.notification_type = #{notificationType}) " +
            "AND (#{isRead} IS NULL OR n.is_read = #{isRead}) " +
            "ORDER BY n.created_at DESC")
    IPage<MemberNotification> selectNotificationPageWithInfo(Page<MemberNotification> page,
                                                              @Param("userId") Long userId,
                                                              @Param("notificationType") String notificationType,
                                                              @Param("isRead") Boolean isRead);

    /**
     * 根据用户ID查询未读通知
     */
    @Select("SELECT n.* " +
            "FROM t_member_notification n " +
            "WHERE n.user_id = #{userId} AND n.is_read = false " +
            "ORDER BY n.created_at DESC " +
            "LIMIT #{limit}")
    List<MemberNotification> selectUnreadByUserId(@Param("userId") Long userId, @Param("limit") Integer limit);

    /**
     * 统计用户未读通知数量
     */
    @Select("SELECT COUNT(*) FROM t_member_notification WHERE user_id = #{userId} AND is_read = false")
    Integer countUnreadByUserId(@Param("userId") Long userId);

    /**
     * 批量标记通知为已读
     */
    @Update("UPDATE t_member_notification SET is_read = true, read_at = NOW() " +
            "WHERE user_id = #{userId} AND id IN (${ids})")
    int markAsRead(@Param("userId") Long userId, @Param("ids") String ids);

    /**
     * 标记用户所有通知为已读
     */
    @Update("UPDATE t_member_notification SET is_read = true, read_at = NOW() " +
            "WHERE user_id = #{userId} AND is_read = false")
    int markAllAsRead(@Param("userId") Long userId);

    /**
     * 统计通知发送情况
     */
    @Select("SELECT notification_type, COUNT(*) as count, " +
            "SUM(CASE WHEN is_read = true THEN 1 ELSE 0 END) as read_count " +
            "FROM t_member_notification " +
            "WHERE DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) " +
            "GROUP BY notification_type")
    List<Map<String, Object>> selectNotificationStats();

    /**
     * 根据用户ID和通知类型查询最新通知
     */
    @Select("SELECT * FROM t_member_notification " +
            "WHERE user_id = #{userId} AND notification_type = #{notificationType} " +
            "ORDER BY created_at DESC LIMIT 1")
    MemberNotification selectLatestByUserIdAndType(@Param("userId") Long userId, 
                                                   @Param("notificationType") String notificationType);

    /**
     * 根据用户ID批量标记为已读
     */
    @Update("UPDATE t_member_notification SET is_read = true, read_at = NOW(), updated_at = NOW() WHERE user_id = #{userId}")
    int markAllAsReadByUserId(@Param("userId") Long userId);
    
    /**
     * 批量删除通知
     */
    @Delete("<script>" +
            "DELETE FROM t_member_notification WHERE id IN " +
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int deleteBatchIds(@Param("ids") List<Long> ids);
} 